import numpy as np
import time

from scipy.cluster.hierarchy import dendrogram, linkage

from matplotlib import pyplot as plt

import xlrd,xlwt
wb=xlrd.open_workbook("数据.xls")
ws=wb.sheet_by_index(1)
j=ws.ncols-1
i=ws.nrows-1
nwb=xlwt.Workbook('xij.xls')
nws=nwb.add_sheet('sheet1')
xi=1
row_data=[]
while xi<=i:
    ls1=ws.row_values(xi)
    ls1.remove(ls1[0])
    row_data.append(ls1)
    xi=xi+1
newI=1
while newI<=i:
    newJ=1
    while newJ<=j:
        oldvalue=ws.cell(newI,newJ).value
        newvalue=(oldvalue-min(row_data[newI-1]))/(max(row_data[newI-1])-min(row_data[newI-1]))
        nws.write(newI,newJ,newvalue)
        newJ=newJ+1
    newI=newI+1

k=1
while k<=i:
    nws.write(k,0,'x'+str(k))
    k=k+1
l=1
while l<=j:
    nws.write(0,l,l)
    l=l+1
nwb.save("更新xi的值.xls")

import math
wb1=xlrd.open_workbook('更新xi的值.xls')
ws1=wb1.sheet_by_index(0)
cols=ws1.ncols
rows=ws1.nrows
nlst=[[]]
i,j=1,1

while j<cols:
    i=1
    ls=[]
    while i<rows:
        ls.append(ws1.cell_value(i,j))
        i=i+1
    nlst.append(ls)
    j=j+1


wb2=xlwt.Workbook("距离.xls")
ws2=wb2.add_sheet("距离")

length=len(nlst[1])
a=1
while a<j:
    b = 1
    while b<=a:
        total=0
        i=0
        while i<=length-1:
            total = total + math.pow((nlst[a][i] - nlst[b][i]), 2)
            i=i+1
        ws2.write(a, b, math.sqrt(total))
        b=b+1
    a=a+1

d=1
while d<j:
    ws2.write(0,d,d)
    ws2.write(d,0,d)
    d=d+1
wb2.save("距离矩阵.xls")

wb3=xlrd.open_workbook('距离矩阵.xls')
ws3=wb3.sheet_by_index(0)
rows3=ws3.nrows
cols3=ws3.ncols
i=rows3-1
j=cols3-1
a=1
lst3=[]
while a<=i:
    b=1
    ls=[]
    while b<a:
        ls.append(ws3.cell_value(a,b))
        b=b+1
    lst3.append(ls)
    a=a+1
lst3.remove(lst3[0])
print(lst3)
min=ws3.cell_value(2,1)
mins=[]
c=1

while c<=i:
    d=1
    while d<c:
        mins.append(ws3.cell_value(c,d))
        d=d+1
    c=c+1
mins.sort()
indexlist=[]
#找每个最小的数
ct=0


cdlist=[]
while ct < len(mins):
    c=1
    while c<=i:
        d=1
        while d<c:

            if mins[ct]==ws3.cell_value(c,d):
                ls=[]
                ls.append(c)
                ls.append(d)
                cdlist.append(ls)

            d=d+1
        c=c+1

    ct=ct+1



# print(indexlist)
# print(cdlist)


z = linkage(cdlist, "average") #用average算法，即类平均法

fig, ax = plt.subplots(figsize=(8,7))  ##画面大小
dendrogram(z, leaf_font_size=2) #画图 字体
plt.title("Hierachial Clustering Dendrogram")
plt.xlabel("Cluster label")
plt.ylabel("Distance")

plt.show()

print(cdlist)
